Excel 2013 is a spreadsheet program that allows you to store, organize, and analyze information. While you may think that Excel is only used by certain people to process complicated data, anyone can learn how to take advantage of Excel’s powerful features. Whether you’re keeping a budget, organizing a training log, or creating an invoice, Excel makes it easy to work with different kinds of data.
Excel 2013 is similar to Excel 2010. If you’ve previously used Excel 2010, Excel 2013 should feel very familiar. But if you are new to Excel, or if you have more experience with older versions, you should first take some time to become familiar with the Excel 2013 interface.
When you open Excel 2013 for the first time, the Excel Start Screen will appear. From here, you’ll be able to create a new workbook, choose a template, and access your recently edited workbooks.
Click the buttons in the interactive below to become familiar with the Excel 2013 interface.
If you’ve previously used Excel 2010 or 2007, Excel 2013 will feel very familiar. It continues to use features like the Ribbon and Quick Access toolbar, where you will find commands to perform common tasks in Excel, as well as Backstage view.
Excel 2013 uses a tabbed Ribbon system instead of traditional menus. The Ribbon contains multiple tabs, each with several groups of commands. You will use these tabs to perform the most common tasks in Excel.
The Home tab gives you access to some of the most commonly used commands for working with data in Excel 2013, including copy and paste, formatting, and number styles. The Home tab is selected by default whenever you open Excel.
The Insert tab allows you to insert charts, tables, sparklines, filters, and more, which can help you visualize and communicate your workbook datagraphically.
The Page Layout tab allows you to change the print formatting of your workbook, including margin width, page orientation, and themes. These commands will be especially helpful when preparing to print a workbook.
The Formulas tab gives you access to the most commonly used functions and formulas in Excel. These commands will help you calculate and analyzenumerical data, like averages and percentages.
The Data tab makes it easy to sort and filter information in your workbook, which can be especially helpful if your project contains a large amount of data.
You can use the Review tab to access Excel’s powerful editing features, including comments and track changes. These features make it easy to share andcollaborate on workbooks.
The View tab allows you to switch between different views for your workbook and freeze panes for easy viewing. These commands will also be helpful when preparing to print a workbook.
Contextual tabs will appear on the Ribbon when working with certain items, like tables and pictures. These tabs contain special command groups that can help you format these items as needed.
Certain programs, such as Adobe Acrobat Reader, may install additional tabs to the Ribbon. These tabs are called Add-ins.
The Ribbon is designed to respond to your current task, but you can choose to minimize it if you find that it takes up too much screen space.
To learn how to add custom tabs and commands to the Ribbon, review our Extra on Customizing the Ribbon.
To learn how to use the Ribbon with touch-screen devices, review our Extra on Enabling Touch Mode.
Located just above the Ribbon, the Quick Access toolbar lets you access common commands no matter which tab is selected. By default, it includes the Save,Undo, and Repeat commands. You can add other commands depending on your preference.
Backstage view gives you various options for saving, opening a file, printing, or sharing your workbooks.
Click the buttons in the interactive below to learn more about using Backstage view.
Excel 2013 has a variety of viewing options that change how your workbook is displayed. You can choose to view any workbook in Normal view, Page Layout view, or Page Break view. These views can be useful for various tasks, especially if you’re planning to print the spreadsheet.
Review the different worksheet view options.
Normal view: This is the default view for all worksheets in Excel.
Page Layout view: This view can help you visualize how your worksheet will appear when printed. You can also add Headers & Footers from this view.
Page Break view: This view makes it easy to change the location of page breaks in your workbook, which is especially helpful when printing a lot of data from Excel.
Excel files are called workbooks. Whenever you start a new project in Excel, you’ll need to create a new workbook. There are several ways to start working with a workbook in Excel 2013. You can choose to create a new workbook—either with a blank workbook or a predesigned template—or open an existingworkbook.
In addition to creating new workbooks, you’ll often need to open a workbook that was previously saved. To learn more about saving workbooks, visit our lesson on Saving and Sharing Workbooks.
If you’ve opened the desired workbook recently, you can browse your Recent Workbooks rather than searching for the file.
If you frequently work with the same workbook, you can pin it to Backstage view for quick access.
will appear next to the workbook. Click the pushpin icon.
You can also pin folders to Backstage view for quick access. From Backstage view, click Open, then locate the folder you wish to pin and click the pushpinicon.
A template is a predesigned spreadsheet you can use to create a new workbook quickly. Templates often include custom formatting and predefinedformulas, so they can save you a lot of time and effort when starting a new project.
You can also browse templates by category or use the search bar to find something more specific.
It’s important to note that not all templates are created by Microsoft. Many are created by third-party providers and even individual users, so some templates may work better than others.
Sometimes you may need to work with workbooks that were created in earlier versions of Microsoft Excel, such as Excel 2003 or Excel 2000. When you open these kinds of workbooks, they will appear in Compatibility mode.
Compatibility mode disables certain features, so you’ll only be able to access commands found in the program that was used to create the workbook. For example, if you open a workbook created in Excel 2003, you can only use tabs and commands found in Excel 2003.
In the image below, you can see that the workbook is in Compatibility mode. This will disable some Excel 2013 features, such as sparklines and slicers.
In order to exit Compatibility mode, you’ll need to convert the workbook to the current version type. However, if you’re collaborating with others who only have access to an earlier version of Excel, it’s best to leave the workbook in Compatibility mode so the format will not change.
If you want access to all of the Excel 2013 features, you can convert the workbook to the 2013 file format.
Note that converting a file may cause some changes to the original layout of the workbook.
Whenever you create a new workbook in Excel, you’ll need to know how to save it in order to access and edit it later. As with previous versions of Excel, you can save files locally to your computer. But unlike older versions, Excel 2013 also lets you save a workbook to the cloud using OneDrive. You can also export andshare workbooks with others directly from Excel.
OneDrive was previously called SkyDrive. There’s nothing fundamentally different about the way OneDrive works—it’s just a new name for an existing service. Over the next few months, you may still see SkyDrive in some Microsoft products.
Excel offers two ways to save a file: Save and Save As. These options work in similar ways, with a few important differences:
It’s important to save your workbook whenever you start a new project or make changes to an existing one. Saving early and often can prevent your work from being lost. You’ll also need to pay close attention to where you save the workbook so it will be easy to find later.
You can also access the Save command by pressing Ctrl+S on your keyboard.
If you want to save a different version of a workbook while keeping the original, you can create a copy. For example, if you have a file named “Sales Data”you could save it as “Sales Data 2” so you’ll be able to edit the new file and still refer back to the original version.
To do this, you’ll click the Save As command in Backstage view. Just like when saving a file for the first time, you’ll need to choose where to save the file and give it a new file name.
If you don’t want to use OneDrive, you may be frustrated that OneDrive is selected as the default location when saving. If you find it inconvenient to selectComputer each time, you can change the default save location so Computer is selected by default.
Excel automatically saves your workbooks to a temporary folder while you are working on them. If you forget to save your changes, or if Excel crashes, you can restore the file using AutoRecover.
By default, Excel autosaves every 10 minutes. If you are editing a workbook for less than 10 minutes, Excel may not create an autosaved version.
If you don’t see the file you need, you can browse all autosaved files from Backstage view. Just select the File tab, click Manage Versions, then chooseRecover Unsaved Workbooks.
By default, Excel workbooks are saved in the .xlsx file type. However, there may be times when you need to use another file type, such as a PDF or Excel 97-2003 workbook. It’s easy to export your workbook from Excel in a variety of file types.
Exporting your workbook as an Adobe Acrobat document, commonly known as a PDF file, can be especially useful if sharing a workbook with someone who does not have Excel. A PDF will make it possible for recipients to view, but not edit, the content of your workbook.
By default, Excel will only export the active worksheet. If you have multiple worksheets and want to save all of them in the same PDF file, click Options in theSave as dialog box. The Options dialog box will appear. Select Entire workbook, then click OK.
Whenever you export a workbook as a PDF, you’ll also need to consider how your workbook data will appear on each page of the PDF, just like printing a workbook. Visit our Page Layout lesson to learn more about what to consider before exporting a workbook as a PDF.
You may also find it helpful to export your workbook in other file types, such as an Excel 97-2003 Workbook if you need to share with people using an older version of Excel, or a .CSV file if you need a plain-text version of your workbook.
You can also use the Save as type: drop-down menu in the Save As dialog box to save workbooks in a variety of file types.
Excel 2013 makes it easy to share and collaborate on workbooks using OneDrive. In the past, if you wanted to share a file with someone you could send it as an email attachment. While convenient, this system also creates multiple versions of the same file, which can be difficult to organize.
When you share a workbook from Excel 2013, you’re actually giving others access to the exact same file. This lets you and the people you share with edit the same workbook without having to keep track of multiple versions.
In order to share a workbook, it must first be saved to your OneDrive.
Click the buttons in the interactive below to learn more about different ways to share a workbook.
Whenever you work with Excel, you’ll enter information, or content, into cells. Cells are the basic building blocks of a worksheet. You’ll need to learn the basics of cells and cell content to calculate, analyze, and organize data in Excel.
Every worksheet is made up of thousands of rectangles, which are called cells. A cell is the intersection of a row and a column. Columns are identified byletters (A, B, C), while rows are identified by numbers (1, 2, 3).
Each cell has its own name, or cell address, based on its column and row. In this example, the selected cell intersects column C and row 5, so the cell address is C5. The cell address will also appear in the Name box. Note that a cell’s column and row headings are highlighted when the cell is selected.
You can also select multiple cells at the same time. A group of cells is known as a cell range. Rather than a single cell address, you will refer to a cell range using the cell addresses of the first and last cells in the cell range, separated by a colon. For example, a cell range that included cells A1, A2, A3, A4, and A5 would be written as A1:A5.
In the images below, two different cell ranges are selected:
If the columns in your spreadsheet are labeled with numbers instead of letters, you’ll need to change the default reference style for Excel. Review our Extra onWhat are Reference Styles? to learn how.
To input or edit cell content, you’ll first need to select the cell.
will appear around the selected cell, and the column heading and row heading will be highlighted. The cell will remain selected until you click another cell in the worksheet.
You can also select cells using the arrow keys on your keyboard.
Sometimes you may want to select a larger group of cells, or a cell range.
Any information you enter into a spreadsheet will be stored in a cell. Each cell can contain several different kinds of content, including text, formatting,formulas, and functions.
You can use the Delete key on your keyboard to delete content from multiple cells at once. The Backspace key will only delete one cell at a time.
There is an important difference between deleting the content of a cell and deleting the cell itself. If you delete the entire cell, the cells below it will shift upand replace the deleted cells.
Excel allows you to copy content that is already entered into your spreadsheet and paste that content to other cells, which can save you time and effort.
Unlike copying and pasting, which duplicates cell content, cutting allows you to move content between cells.
You can also access additional paste options, which are especially convenient when working with cells that contain formulas or formatting.
Rather than choosing commands from the Ribbon, you can access commands quickly by right-clicking. Simply select the cell(s) you wish to format, then right-click the mouse. A drop-down menu will appear, where you’ll find several commands that are also located on the Ribbon.
Rather than cutting, copying, and pasting, you can drag and drop cells to move their contents.
to a black cross with four arrows
.
There may be times when you need to copy the content of one cell to several other cells in your worksheet. You could copy and paste the content into each cell, but this method would be very time consuming. Instead, you can use the fill handle to quickly copy and paste content to adjacent cells in the same row or column.
The fill handle can also be used to continue a series. Whenever the content of a row or column follows a sequential order, like numbers (1, 2, 3) or days(Monday, Tuesday, Wednesday), the fill handle can guess what should come next in the series. In many cases, you may need to select multiple cells before using the fill handle to help Excel determine the series order. In our example below, the fill handle is used to extend a series of dates in a column.
A new feature in Excel 2013, Flash Fill can enter data automatically into your worksheet, saving you a lot of time and effort. Just like the fill handle, Flash Fillcan guess what kind of information you’re entering into your worksheet. In the example below, we’ll use Flash Fill to create a list of first names using a list of existing email addresses.
To modify or undo Flash Fill, click the Flash Fill button next to recently added Flash Fill data.
When working with a lot of data in Excel, it can be difficult and time consuming to locate specific information. You can easily search your workbook using theFind feature, which also allows you to modify content using the Replace feature.
In our example, we’ll use the Find command to locate a specific name in a long list of employees.
You can also access the Find command by pressing Ctrl+F on your keyboard.
Click Options to see advanced search criteria in the Find and Replace dialog box.
At times, you may discover that you’ve repeatedly made a mistake throughout your workbook (such as misspelling someone’s name), or that you need to exchange a particular word or phrase for another. You can use Excel’s Find and Replace feature to make quick revisions. In our example, we’ll use Find and Replace to correct a list of email addresses.
By default, every row and column of a new workbook is always set to the same height and width. Excel allows you to modify column width and row height in different ways, including wrapping text and merging cells.
In our example below, some of the content in column A cannot be displayed. We can make all of this content visible by changing the width of column A.
becomes a double arrow 
.
If you see pound signs (#######) in a cell, it means that the column is not wide enough to display the cell content. Simply increase the column width to show the cell content.
The AutoFit feature will allow you to set a column’s width to fit its content automatically.
becomes a double arrow .
You can also AutoFit the width for several columns at the same time. Simply select the columns you would like to AutoFit, then select the AutoFit Column Width command from the Format drop-down menu on the Home tab. This method can also be used for Row height.
becomes a double arrow 
.
Rather than resizing rows and columns individually, you can modify the height and width of every row and column at the same time. This method allows you to set a uniform size for every row and column in your worksheet. In our example, we will set a uniform row height.
just below the formula bar to select every cell in the worksheet.
becomes a double arrow .
After you’ve been working with a workbook for a while, you may find that you want to insert new columns or rows, delete certain rows or columns, movethem to a different location in the worksheet, or even hide them.
When inserting new rows, columns, or cells, you will see the Insert Options button 
next to the inserted cells. This button allows you to choose how Excel formats these cells. By default, Excel formats inserted rows with the same formatting as the cells in the row above. To access more options, hover your mouse over the Insert Options button, then click the drop-down arrow.
When inserting rows and columns, make sure you select the entire row or column by clicking the heading. If you select only a cell in the row or column, theInsert command will only insert a new cell.
It’s easy to delete any row that you no longer need in your workbook.
It’s important to understand the difference between deleting a row or column and simply clearing its contents. If you want to remove the content of a row or column without causing others to shift, right-click a heading, then select Clear Contents from the drop-down menu.
Sometimes you may want to move a column or row to rearrange the content of your worksheet. In our example we will move a column, but you can move a row in the same way.
You can also access the Cut and Insert commands by right-clicking the mouse and then selecting the desired commands from the drop-down menu.
At times, you may want to compare certain rows or columns without changing the organization of your worksheet. Excel allows you to hide rows and columns as needed. In our example, we’ll hide columns C and D to make it easier to compare columns A, B, and E.
Whenever you have too much cell content to be displayed in a single cell, you may decide to wrap the text or merge the cell rather than resizing a column. Wrapping the text will automatically modify a cell’s row height, allowing cell contents to be displayed on multiple lines. Merging allows you to combine a cell with adjacent, empty cells to create one large cell.
In our example below, we’ll wrap the text of the cells in column D so the entire address can be displayed.
Click the Wrap Text command again to unwrap the text.
In our example below, we’ll merge cell A1 with cells B1:E1 to create a title heading for our worksheet.
Click the drop-down arrow next to the Merge & Center command on the Home tab. The Merge drop-down menu will appear. From here, you can choose to:
All cell content uses the same formatting by default, which can make it difficult to read a workbook with a lot of information. Basic formatting can customize the look and feel of your workbook, allowing you to draw attention to specific sections and making your content easier to view and understand. You can also apply number formatting to tell Excel exactly what type of data you’re using in the workbook, such as percentages (%), currency ($), and so on.
By default, the font of each new workbook is set to Calibri. However, Excel provides a variety of other fonts you can use to customize your cell text. In the example below, we’ll format our title cell to help distinguish it from the rest of the worksheet.
When creating a workbook in the workplace, you’ll want to select a font that is easy to read. Along with Calibri, standard reading fonts include Cambria, Times New Roman, and Arial.
You can also use the Increase Font Size and Decrease Font Size commands or enter a custom font size using your keyboard.
Select More Colors at the bottom of the menu to access additional color options.
You can also press Ctrl+B on your keyboard to make selected text bold, Ctrl+I to apply italics, and Ctrl+U to apply an underline.
By default, any text entered into your worksheet will be aligned to the bottom-left of a cell. Any numbers will be aligned to the bottom-right of a cell. Changing the alignment of your cell content allows you to choose how the content is displayed in any cell, which can make your cell content easier to read.
Learn more about the different text alignment options.
Left align: Aligns content to the left border of the cell
Center Align: Aligns to the center of cell
Right Align: Aligns to the right of cell
In our examples below, we’ll modify the alignment of our title cell to create a more polished look and further distinguish it from the rest of the worksheet.
You can apply both vertical and horizontal alignment settings to any cell.
Cell borders and fill colors allow you to create clear and defined boundaries for different sections of your worksheet. In our examples below, we’ll add cell borders and fill color to our header cells to help distinguish them from the rest of the worksheet.
You can draw borders and change the line style and color of borders with the Draw Borders tools at the bottom of the Borders drop-down menu.
Rather than formatting cells manually, you can use Excel’s predesigned cell styles. Cell styles are a quick way to include professional formatting for different parts of your workbook, such as titles and headers.
In our example, we’ll apply a new cell style to our existing title and header cells.
Applying a cell style will replace any existing cell formatting except for text alignment. You may not want to use cell styles if you’ve already added a lot of formatting to your workbook.
One of the most powerful tools in Excel is the ability to apply specific formatting for text and numbers. Instead of displaying all cell content in exactly the same way, you can use formatting to change the appearance of dates, times, decimals, percentages (%), currency ($), and much more.
In our example, we’ll change the number format for several cells to modify the way dates are displayed.
Click the buttons in the interactive below to learn about different text and number formatting options.
Every workbook contains at least one worksheet by default. When working with a large amount of data, you can create multiple worksheets to help organize your workbook and make it easier to find content. You can also group worksheets to quickly add information to multiple worksheets at the same time.
Whenever you create a new Excel workbook, it will contain one worksheet named Sheet1. You can rename a worksheet to better reflect its content. In our example, we will create a training log organized by month.
To change the default number of worksheets, navigate to Backstage view, click Options, then choose the desired number of worksheets to include in each new workbook.
If you wish to prevent specific worksheets from being edited or deleted, you can protect them by right-clicking the desired worksheet and then selectingProtect sheet from the worksheet menu.
If you need to duplicate the content of one worksheet to another, Excel allows you to copy an existing worksheet.
You can also copy a worksheet to an entirely different workbook. You can select any workbook that is currently open from the To book: drop-down menu.
Sometimes you may want to move a worksheet to rearrange your workbook.
.
appears above the desired location.
You can change a worksheet’s color to help organize your worksheets and make your workbook easier to navigate.
The worksheet color is considerably less noticeable when the worksheet is selected. Select another worksheet to see how the color will appear when the worksheet is not selected.
You can work with each worksheet individually, or you can work with multiple worksheets at the same time. Worksheets can be combined together into agroup. Any changes made to one worksheet in a group will be made to every worksheet in the group.
In our example, employees need to receive training every three months, so we’ll create a worksheet group for those employees. When we add the names of the employees to one worksheet, they’ll be added to the other worksheets in the group as well.
While worksheets are grouped, you can navigate to any worksheet within the group. Any changes made to one worksheet will appear on every worksheet in the group. However, if you select a worksheet that is not in the group, all of your worksheets will become ungrouped.
Many of the commands you’ll use to prepare your workbook for printing and PDF export can be found on the Page Layout tab. These commands let you control the way your content will appear on a printed page, including the page orientation and margin size. Other page layout options, such as print titlesand page breaks, can help make your workbook easier to read.
Before you start modifying a workbook’s page layout, you may want to view the workbook in Page Layout view, which can help you visualize your changes.
Excel offers two page orientation options: landscape and portrait. Landscape orients the page horizontally, while Portrait orients the page vertically. Portrait is especially helpful for worksheets with a lot of rows, while Landscape is best for worksheets with a lot of columns. In the example below, Portrait orientation works best because the worksheet includes more rows than columns.
A margin is the space between your content and the edge of the page. By default, every workbook’s margins are set to Normal, which is a one-inch space between the content and each edge of the page. Sometimes you may need to adjust the margins to make your data fit more comfortably on the page. Excel includes a variety of predefined margin sizes.
Excel also allows you to customize the size of your margins in the Page Setup dialog box.
If your worksheet uses title headings, it’s important to include these headings on each page of your printed worksheet. It would be difficult to read a printed workbook if the title headings appeared only on the first page. The Print Titles command allows you to select specific rows and columns to appear on each page.
and the Page Setup dialog box will be collapsed. Select the row(s) you want to repeat at the top of each printed page. In our example, we’ll select row 1.
If you need to print different parts of your workbook across separate pages, you can insert a page break. There are two types of page breaks: vertical andhorizontal. Vertical page breaks separate columns, while horizontal page breaks separate rows. In our example, we’ll insert a horizontal page break.
When viewing your workbook in Normal view, inserted page breaks are represented by a solid gray line, while automatic page breaks are represented by adashed line.
You can make your workbook easier to read and look more professional by including headers and footers. The header is a section of the workbook that appears in the top margin, while the footer appears in the bottom margin. Headers and footers generally contain information such as page number, date, and workbook name.
Excel uses the same tools as Microsoft Word to modify headers and footers.
There may be times when you want to print a workbook to view and share your data offline. Once you’ve chosen your page layout settings, it’s easy to preview and print a workbook from Excel using the Print pane.
Click the buttons in the interactive below to learn more about using the Print pane.
Before you print an Excel workbook, it’s important to decide exactly what information you want to print. For example, if you have multiple worksheets in your workbook, you will need to decide if you want to print the entire workbook or only active worksheets. There may also be times when you want to print only a selection of content from your workbook.
Worksheets are considered active when selected.
In our example, we’ll print a selection of content related to upcoming softball games in July.
If you prefer, you can also set the print area in advance so you’ll be able to visualize which cells will be printed as you work in Excel. Simply select the cells you want to print, click the Page Layout tab, select the Print Area command, then choose Set Print Area.
On occasion, you may need to make small adjustments from the Print pane to fit your workbook content neatly onto a printed page. The Print pane includes several tools to help fit and scale your content, such as scaling and page margins.
If some of your content is being cut off by the printer, you can use scaling to fit your workbook to the page automatically.
Keep in mind that worksheets will become more difficult to read as they are scaled down, so you may not want to use this option when printing a worksheet with a lot of information.
Sometimes you may only need to adjust a single margin to make your data fit more comfortably. You can modify individual page margins from the Previewpane.
until the cursor becomes a double arrow . In our example, we’ll modify the left margin to fit an additional column on the page.
One of the most powerful features in Excel is the ability to calculate numerical information using formulas. Just like a calculator, Excel can add, subtract, multiply, and divide. In this lesson, we’ll show you how to use cell references to create simple formulas.
Excel uses standard operators for formulas, such as a plus sign for addition (+), a minus sign for subtraction (–), an asterisk for multiplication (*), a forward slash for division (/), and a caret (^) for exponents.
All formulas in Excel must begin with an equals sign (=). This is because the cell contains, or is equal to, the formula and the value it calculates.
While you can create simple formulas in Excel manually (for example, =2+2 or =5*5), most of the time you will use cell addresses to create a formula. This is known as making a cell reference. Using cell references will ensure that your formulas are always accurate because you can change the value of referenced cells without having to rewrite the formula.
By combining a mathematical operator with cell references, you can create a variety of simple formulas in Excel. Formulas can also include a combination of cell references and numbers, as in the examples below:
In our example below, we’ll use a simple formula and cell references to calculate a budget.
If the result of a formula is too large to be displayed in a cell, it may appear as pound signs (#######) instead of a value. This means that the column is not wide enough to display the cell content. Simply increase the column width to show the cell content.
The true advantage of cell references is that they allow you to update data in your worksheet without having to rewrite formulas. In the example below, we’ve modified the value of cell B1 from $1,200 to $1,800. The formula in B3 will automatically recalculate and display the new value in cell B3.
Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas.
Rather than typing cell addresses manually, you can point and click on the cells you wish to include in your formula. This method can save a lot of time and effort when creating formulas. In our example below, we’ll create a formula to calculate the cost of ordering several boxes of plastic silverware.
Formulas can also be copied to adjacent cells with the fill handle, which can save a lot of time and effort if you need to perform the same calculation multiple times in a worksheet.
Sometimes you may want to modify an existing formula. In the example below, we’ve entered an incorrect cell address in our formula, so we’ll need to correct it.
in the formula bar.
If you change your mind, you can press the Esc key on your keyboard or click the Cancel command 
in the formula bar to avoid accidentally making changes to your formula.
To show all of the formulas in a spreadsheet, you can hold the Ctrl key and press ` (grave accent). The grave accent key is usually located in the upper-left corner of the keyboard. You can press Ctrl+` again to switch back to the normal view.
A simple formula is a mathematical expression with one operator, such as 7+9. A complex formula has more than one mathematical operator, such as 5+2*8. When there is more than one operation in a formula, the order of operations tells Excel which operation to calculate first. In order to use Excel to calculate complex formulas, you will need to understand the order of operations.
Excel calculates formulas based on the following order of operations:
A mnemonic that can help you remember the order is PEMDAS, or Please Excuse My Dear Aunt Sally.
To learn more about how the order of operations is used to calculate formulas in Excel.
While this formula may look really complicated, we can use the order of operations step by step to find the right answer.
As you can see, the formula already looks a bit simpler. Next, we’ll look to see if there are any exponents. There’s one: 2^2=4.
Next, we’ll solve any multiplication and division, working from left to right. Because the division operation comes before the multiplication, it is calculated first: 3/4=0.75.
Now, we’ll solve our remaining multiplication operation: 0.75*4=3.
Next, we’ll calculate any addition or subtraction, again working from left to right. Addition comes first: 10+3=13.
Finally, we have one remaining subtraction operation: 13-1=12.
And now we have our answer: 12. This is the exact same result you would get if you entered the formula into Excel.
In the example below, we will demonstrate how Excel solves a complex formula using the order of operations. Here, we want to calculate the cost of sales taxfor a catering invoice. To do this, we’ll write our formula as =(D2+D3)*0.075 in cell D4. This formula will add the prices of our items together and then multiply that value by the 7.5% tax rate (which is written as 0.075) to calculate the cost of sales tax.
Excel follows the order of operations and first adds the values inside the parentheses: (44.85+39.90) = $84.75. Then, it multiplies that value by the tax rate:$84.75*0.075. The result will show that the sales tax is $6.36.
It is especially important to enter complex formulas with the correct order of operations. Otherwise, Excel will not calculate the results accurately. In our example, if the parentheses are not included, the multiplication is calculated first and the result is incorrect. Parentheses are the best way to define which calculations will be performed first in Excel.
In our example below, we will use cell references along with numerical values to create a complex formula that will calculate the total cost for a catering invoice. The formula will calculate the cost for each menu item and then add those values together.
You can add parentheses to any equation to make it easier to read. While it won’t change the result of the formula in this example, we could enclose the multiplication operations within parentheses to clarify that they will be calculated before the addition.
Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas.
There are two types of cell references: relative and absolute. Relative and absolute references behave differently when copied and filled to other cells. Relative references change when a formula is copied to another cell. Absolute references, on the other hand, remain constant, no matter where they are copied.
By default, all cell references are relative references. When copied across multiple cells, they change based on the relative position of rows and columns. For example, if you copy the formula =A1+B1 from row 1 to row 2, the formula will become =A2+B2. Relative references are especially convenient whenever you need to repeat the same calculation across multiple rows or columns.
In the following example, we want to create a formula that will multiply each item’s price by the quantity. Rather than creating a new formula for each row, we can create a single formula in cell D2 and then copy it to the other rows. We’ll use relative references so the formula correctly calculates the total for each item.
You can double-click the filled cells to check their formulas for accuracy. The relative cell references should be different for each cell, depending on their rows.
There may be times when you do not want a cell reference to change when filling cells. Unlike relative references, absolute references do not change when copied or filled. You can use an absolute reference to keep a row and/or column constant.
An absolute reference is designated in a formula by the addition of a dollar sign ($). It can precede the column reference, the row reference, or both.
You will generally use the $A$2 format when creating formulas that contain absolute references. The other two formats are used much less frequently.
When writing a formula, you can press the F4 key on your keyboard to switch between relative and absolute cell references. This is an easy way to quickly insert an absolute reference.
In our example, we’ll use the 7.5% sales tax rate in cell E1 to calculate the sales tax for all items in column D. We’ll need to use the absolute cell reference $E$1in our formula. Since each formula is using the same tax rate, we want that reference to remain constant when the formula is copied and filled to other cells in column D.
You can double-click the filled cells to check their formulas for accuracy. The absolute reference should be the same for each cell, while the other references are relative to the cell’s row.
Be sure to include the dollar sign ($) whenever you’re making an absolute reference across multiple cells. The dollar signs were omitted in the example below. This caused Excel to interpret it as a relative reference, producing an incorrect result when copied to other cells.
Excel allows you to refer to any cell on any worksheet, which can be especially helpful if you want to reference a specific value from one worksheet to another. To do this, you’ll simply need to begin the cell reference with the worksheet name followed by an exclamation point (!). For example, if you wanted to reference cell A1 on Sheet1, its cell reference would be Sheet1!A1.
Note that if a worksheet name contains a space, you will need to include single quotation marks (‘ ‘) around the name. For example, if you wanted to reference cell A1 on a worksheet named July Budget, its cell reference would be ‘July Budget’!A1.
In our example below, we’ll refer to a cell with a calculated value between two worksheets. This will allow us to use the exact same value on two different worksheets without rewriting the formula or copying data between worksheets.
If you rename your worksheet at a later point, the cell reference will be updated automatically to reflect the new worksheet name.
If you enter a worksheet name incorrectly, the #REF! error will appear in the cell. In our example below, we’ve mistyped the name of the worksheet. Click theError button 
and select the desired option from the drop-down menu to edit or ignore the error.
A function is a predefined formula that performs calculations using specific values in a particular order. Excel includes many common functions that can be useful for quickly finding the sum, average, count, maximum value, and minimum value for a range of cells. In order to use functions correctly, you’ll need to understand the different parts of a function and how to create arguments to calculate values and cell references.
In order to work correctly, a function must be written a specific way, which is called the syntax. The basic syntax for a function is an equals sign (=), thefunction name (SUM, for example), and one or more arguments. Arguments contain the information you want to calculate. The function in the example below would add the values of the cell range A1:A20.
Arguments can refer to both individual cells and cell ranges and must be enclosed within parentheses. You can include one argument or multiple arguments, depending on the syntax required for the function.
For example, the function =AVERAGE(B1:B9) would calculate the average of the values in the cell range B1:B9. This function contains only one argument.
Multiple arguments must be separated by a comma. For example, the function =SUM(A1:A3, C1:C2, E2) will add the values of all the cells in the three arguments.
Excel has a variety of functions available. Here are some of the most common functions you’ll use:
In our example below, we’ll create a basic function to calculate the average price per unit for a list of recently ordered items using the AVERAGE function.
Excel will not always tell you if your formula contains an error, so it’s up to you to check all of your formulas. To learn how to do this, read the Double-Check Your Formulas lesson from our Excel Formulas tutorial.
The AutoSum command allows you to automatically insert the most common functions into your formula, including SUM, AVERAGE, COUNT, MIN, and MAX. In our example below, we’ll create a function to calculate the total cost for a list of recently ordered items using the SUM function.
The AutoSum command can also be accessed from the Formulas tab on the Ribbon.
While there are hundreds of functions in Excel, the ones you use most frequently will depend on the type of data your workbooks contains. There is no need to learn every single function, but exploring some of the different types of functions will be helpful as you create new projects. You can search for functionsby category, such as Financial, Logical, Text, Date & Time, and more from the Function Library on the Formulas tab.
Click the buttons in the interactive below to learn more about the different types of functions in Excel.
In our example below, we’ll use a function to calculate the number of business days it took to receive items after they were ordered. In our example, we’ll use the dates in columns B and C to calculate the delivery time in column D.
Like formulas, functions can be copied to adjacent cells. Hover the mouse over the cell that contains the function, then click, hold, and drag the fill handle over the cells you wish to fill. The function will be copied, and values for those cells will be calculated relative to their rows or columns.
If you’re having trouble finding the right function, the Insert Function command allows you to search for functions using keywords. While it can be extremely useful, this command is sometimes a little difficult to use. If you don’t have much experience with functions, you may have more success browsing theFunction Library instead. For more advanced users, however, the Insert Function command can be a powerful way to find a function quickly.
In our example below, we want to find a function that will count the total number of items ordered. We want to count the cells in the Item column, which uses text. We cannot use the basic COUNT function because it will only count cells with numerical information. Therefore, we will need to find a function that counts the total number of cells within a cell range.
If you’re comfortable with basic functions, you may want to try a more advanced one like VLOOKUP.
Whenever you’re working with a lot of data, it can be difficult to compare information in your workbook. Fortunately, Excel includes several tools that make it easier to view content from different parts of your workbook at the same time, such as the ability to freeze panes and split your worksheet.
You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you’ll be able to scroll through your content while continuing to view the frozen cells.
To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu.
If you only need to freeze the top row (row 1) or first column (column A) in the worksheet, you can simply select Freeze Top Row or Freeze First Columnfrom the drop-down menu.
If your workbook contains a lot of content, it can sometimes be difficult to compare different sections. Excel includes additional options to make your workbooks easier to view and compare. For example, you can choose to open a new window for your workbook or split a worksheet into separate panes.
Excel allows you to open multiple windows for a single workbook at the same time. In our example, we’ll use this feature to compare two differentworksheets from the same workbook.
If you have several windows open at the same time, you can use the Arrange All command to rearrange them quickly.
Sometimes you may want to compare different sections of the same workbook without creating a new window. The Split command allows you to divide the worksheet into multiple panes that scroll separately.
After creating a split, you can click and drag the vertical and horizontal dividers to change the size of each section.
To remove the split, click the Split command again.
As you add more content to a worksheet, organizing that information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you could organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in many other ways.
When sorting data, it’s important to first decide if you would like the sort to apply to the entire worksheet or just a cell range.
In our example, we’ll sort a T-shirt order form alphabetically by Last Name (column C).
to Sort A to Z, or the Descending command 
to Sort Z to A. In our example, we’ll click the Ascending command.
In our example, we’ll select a separate table in our T-shirt order form to sort the number of shirts that were ordered on different dates.
If your data isn’t sorting properly, double-check your cell values to make sure they are entered into the worksheet correctly. Even a small typo could cause problems when sorting a large worksheet. In the example below, we forgot to include a hyphen in cell A18, causing our sort to be slightly inaccurate.
Sometimes you may find that the default sorting options can’t sort data in the order you need. Fortunately, Excel allows you to create a custom list to define your own sorting order.
In our example below, we want to sort the worksheet by T-Shirt Size (column D). A regular sort would organize the sizes alphabetically, which would be incorrect. Instead, we’ll create a custom list to sort from smallest to largest.
You can also choose to sort your worksheet by formatting rather than cell content. This can be especially helpful if you add color coding to certain cells. In our example below, we’ll sort by cell color to quickly see which T-shirt orders have outstanding payments.
If you need more control over how your data is sorted, you can add multiple levels to any sort. This allows you to sort your data by more than one column.
In our example below, we’ll sort the worksheet by Homeroom Number (column A), then by Last Name (column C).
If you need to change the order of a multilevel sort, it’s easy to control which column is sorted first. Simply select the desired column, then click the Move Upor Move Down arrow to adjust its priority.
or descending order. If you are using the example, sort by Homeroom # (column A).
If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need.
In our example, we’ll apply a filter to an equipment log worksheet to display only the laptops and projectors that are available for checkout.
will appear in the header cell for each column.
Filtering options can also be accessed from the Sort & Filter command on the Home tab.
Filters are cumulative, which means you can apply multiple filters to help narrow down your results. In this example, we’ve already filtered our worksheet to show laptops and projectors, and we’d like to narrow it down further to only show laptops and projectors that were checked out in August.
After applying a filter, you may want to remove, or clear, it from your worksheet so you’ll be able to filter content in different ways.
To remove all filters from your worksheet, click the Filter command on the Data tab.
If you need to filter for something specific, basic filtering may not give you enough options. Fortunately, Excel includes many advanced filtering tools, including search, text, date, and number filtering, which can narrow your results to help find exactly what you need.
Excel allows you to search for data that contains an exact phrase, number, date, and more. In our example, we’ll use this feature to show only Saris brand products in our equipment log.
Advanced text filters can be used to display more specific information, such as cells that contain a certain number of characters, or data that excludes a specific word or number. In our example, we’ve already filtered our worksheet to only show items with “Other” in the Type column, but we’d like to exclude any item containing the word case.
Advanced date filters can be used to view information from a certain time period, such as last year, next quarter, or between two dates. In this example, we will use advanced date filters to view only equipment that has been checked out today.
If you’re working along with the example file, your results will be different from the images above. If you want, you can change some of the dates so the filter will give more results.
Advanced number filters allow you to manipulate numbered data in different ways. In this example, we will display only certain kinds of equipment based on the range of ID numbers.
Worksheets with a lot of content can sometimes feel overwhelming and even become difficult to read. Fortunately, Excel can organize data in groups, allowing you to easily show and hide different sections of your worksheet. You can also summarize different groups using the Subtotal command and create anoutline for your worksheet.
To ungroup data, select the grouped rows or columns, then click the Ungroup command.
.
.
The Subtotal command allows you to automatically create groups and use common functions like SUM, COUNT, and AVERAGE to help summarize your data. For example, the Subtotal command could help to calculate the cost of office supplies by type from a large inventory order. The Subtotal command will create a hierarchy of groups, known as an outline, to help organize your worksheet.
Your data must be correctly sorted before using the Subtotal command, so you may want to review our lesson on Sorting Data to learn more.
In our example, we will use the Subtotal command with a T-shirt order form to determine how many T-shirts were ordered in each size (Small, Medium, Large, and X-Large). This will create an outline for our worksheet with a group for each T-shirt size and then count the total number of shirts in each group.
When you create subtotals, your worksheet it is divided into different levels. You can switch between these levels to quickly control how much information is displayed in the worksheet by clicking the Level buttons 
to the left of the worksheet. In our example, we’ll switch between all three levels in our outline. While this example contains only three levels, Excel can accommodate up to eight.
You can also use the Show and Hide Detail buttons to show and hide the groups within the outline.
Sometimes you may not want to keep subtotals in your worksheet, especially if you want to reorganize data in different ways. If you no longer wish to use subtotaling, you’ll need remove it from your worksheet.
To remove all groups without deleting the subtotals, click the Ungroup command drop-down arrow, then choose Clear Outline.
Once you’ve entered information into a worksheet, you may want to format your data as a table. Just like regular formatting, tables can improve the look and feel of your workbook, but they’ll also help to organize your content and make your data easier to use. Excel includes several tools and predefined table styles, allowing you to create tables quickly and easily.
Tables include filtering by default. You can filter your data at any time using the drop-down arrows in the header cells.
It’s easy to modify the look and feel of any table after adding it to a worksheet. Excel includes different options for customizing a table, including adding rows or columns, changing the table style, and more.
If you need to fit more content in your table, Excel allows you to modify the table size by including additional rows and columns. There are two simple ways to change the table size:
You can turn various options on or off to change the appearance of any table. There are six options: Header Row, Total Row, Banded Rows, First Column,Last Column, and Banded Columns.
These options can affect your table style in various ways, depending on the type of content in your table. You may need to experiment with a few different options to find the exact style you want.
Sometimes you may not want to use the additional features included with tables, such as the Sort and Filter drop-down arrows. You can remove a table from the workbook while still preserving the table’s formatting elements, like font and cell color.
It can often be difficult to interpret Excel workbooks that contain a lot of data. Charts allow you to illustrate your workbook data graphically, which makes it easy to visualize comparisons and trends.
Excel has several different types of charts, allowing you to choose the one that best fits your data. In order to use charts effectively, you’ll need to understand how different charts are used.
Learn more about the types of charts in Excel.
Excel has a variety of chart types, each with its own advantages. Click the arrows to see some of the different types of charts available in Excel.
Line charts are ideal for showing trends. The data points are connected with lines, making it easy to see whether values are increasing or decreasing over time.
Pie charts make it easy to compare proportions. Each value is shown as a slice of the pie, so it’s easy to see which values make up the percentage of a whole.
Bar charts work just like Column charts, but they use horizontal bars instead of vertical bars.
Area charts are similar to line charts, except that the areas under the lines are filled in.
Surface charts allow you to display data across a 3D landscape. They work best with large data sets, allowing you to see a variety of information at the same time.
If you’re not sure which type of chart to use, the Recommended Charts command will suggest several different charts based on the source data.
After inserting a chart, there are several things you may want to change about the way your data is displayed. It’s easy to edit a chart’s layout and style from the Design tab.
You can also use the chart formatting shortcut buttons to quickly add chart elements, change the chart style, and filter the chart data.
There are lots of other ways to customize and organize your charts. For example, Excel allows you to rearrange a chart’s data, change the chart type, and even move the chart to a different location in the workbook.
Sometimes you may want to change the way charts group your data. For example, in the chart below, the Book Sales data are grouped by year, with columns for each genre. However, we could switch the rows and columns so the chart will group the data by genre, with columns for each year. In both cases, the chart contains the same data—it’s just organized differently.
If you find that your data isn’t well suited to a certain chart, it’s easy to switch to a new chart type. In our example, we’ll change our chart from a Column chart to a Line chart.
Whenever you insert a new chart, it will appear as an object on the same worksheet that contains its source data. Alternatively, you can move the chart to anew worksheet to help keep your data organized.
Sometimes you may want to analyze and view trends in your data without creating an entire chart. Sparklines are miniature charts that fit into a single cell. Because they’re so compact, it’s easy to include several sparklines in a workbook.
There are three different types of sparklines: Line, Column, and Win/Loss. Line and Column work the same as line and column charts. Win/Loss is similar toColumn, except it only shows whether each value is positive or negative instead of how high or low the values are. All three types can display markers at important points, such as the highest and lowest points, to make them easier to read.
Sparklines have certain advantages over charts. For example, imagine you have 1000 rows of data. A traditional chart would have 1000 data series to represent all of the rows, making relevant data difficult to find. But if you placed a sparkline on each row, it will be right next to its source data, making it easy to seerelationships and trends for multiple data series at the same time.
In the image below, the chart is extremely cluttered and difficult to follow, but the sparklines allow you to clearly follow each salesperson’s data.
Sparklines are ideal for situations when you need a clear overview of the data at a glance and when you don’t need all of the features of a full chart. On the other hand, charts are ideal for situations when you want to represent the data in greater detail, and they are often better for comparing different data series.
Generally, you will have one sparkline for each row, but you can create as many as you want in any location. Just like formulas, it’s usually easiest to create asingle sparkline and then use the fill handle to create sparklines for the adjacent rows. In our example, we’ll create sparklines to help visualize trends in sales over time for each salesperson.
It’s easy to change the way sparklines appear in your worksheet. Excel allows you to customize a sparkline’s markers, style, type, and more.
Certain points on a sparkline can be emphasized with markers, or dots, making the sparkline more readable. For example, in a line with a lot of ups and downs, it might be difficult to tell which values are the highest and lowest points. Showing the High Point and Low Point will make them easier to identify.
Some sparkline types will be better suited for certain types of data. For example, Win/Loss is best suited for data where there could be positive and negativevalues (such as net earnings).
By default, each sparkline is scaled to fit the maximum and minimum values of its own data source: The maximum value will go to the top of the cell, while the minimum will go to the bottom. However, this doesn’t show how high or low the values are when compared to the other sparklines. Excel allows you to modify the sparkline display range, which makes it easier to compare sparklines.
Suppose someone asked you to proofread or collaborate on a workbook. If you had a printed copy, you might use a red pen to edit cell data, mark spelling errors, or add comments in the margins. Excel allows you to do all of these things electronically using the Track Changes and Comments features.
When you turn on the Track Changes feature, every cell you edit will be highlighted with a unique border and indicator. Selecting a marked cell will show the details of the change. This allows you and other reviewers to see what’s been changed before accepting the revisions permanently.
There are some changes Excel cannot track. Before using this feature, you may want to review Microsoft’s list of changes that Excel does not track or highlight.
You cannot use Track Changes if your workbook includes tables. To remove a table, select it, click the Design tab, then click Convert to Range.
When you turn on Track Changes, your workbook will be “shared” automatically. Shared workbooks are designed to be stored where other users can access and edit the workbook at the same time, such as a network. However, you can also track changes in a local or personal copy, as seen throughout this lesson.
You can also view changes on a new worksheet, sometimes called the Tracked Changes history. The history lists everything in your worksheet that has been changed, including the “old value” (previous cell content) and the “new value” (current cell content).
To remove the History worksheet from your workbook, you can either save your workbook again or uncheck the box next to List changes on a new sheet in the Highlight Changes dialog box.
Tracked changes are really just “suggested” changes. To become permanent, the changes must be accepted. On the other hand, the original author may disagree with some of the tracked changes and choose to reject them.
To accept or reject all the changes at once, click Accept All or Reject All in the Accept or Reject Changes dialog box.
Turning off Track Changes will remove any tracked changes in your workbook. You will not be able to view, accept, or reject changes; instead, all changes will all be accepted automatically. Always review the changes in your worksheet before turning off Track Changes.
Sometimes you may want to add a comment to provide feedback instead of editing the contents of a cell. While often used in combination with Track Changes, you don’t necessarily need to have Track Changes turned on to use comments.
You can also choose to show and hide individual comments by selecting the desired cell and then clicking the Show/Hide Comment command.
Before sharing a workbook, you’ll want to make sure it doesn’t include any spelling errors or information you wish to keep private. Fortunately, Excel includes several tools to help finalize and protect your workbook, such as Spell Check and the Document Inspector.
If there are no appropriate suggestions, you can also enter the correct spelling manually.
Spell Check isn’t always correct. It will sometimes mark certain words as incorrect, even if they’re spelled correctly. This often happens with names, which may not be in the dictionary. You can choose not to change a spelling “error” using one of three options:
Whenever you create or edit a workbook, certain personal information may be added to the file automatically. You can use the Document Inspector to remove this kind of information before sharing a workbook with others.
Because some changes may be permanent, it’s a good idea to save an additional copy of your workbook before using the Document Inspector to remove information.
By default, anyone with access to your workbook will be able to open, copy, and edit its content unless you protect it. There are many different ways to protect a workbook, depending on your needs.
Marking a workbook as final will not prevent someone from editing it. If you want to prevent people from editing it, you can use the Restrict Access option instead.
Let’s imagine you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts and sparklines, conditional formatting provides another way to visualize data and make worksheets easier to understand.
Conditional formatting allows you to automatically apply formatting—such as colors, icons, and data bars—to one or more cells based on the cell value. To do this, you’ll need to create a conditional formatting rule. For example, a conditional formatting rule might be: “If the value is less than $2000, color the cell red.” By applying this rule, you’d be able to quickly see which cells contain values under $2000.
In our example, we have a worksheet containing sales data, and we’d like to see which salespeople are meeting their monthly sales goals. The sales goal is $4000 per month, so we’ll create a conditional formatting rule for any cells containing a value higher than 4000.
You can apply multiple conditional formatting rules to a cell range or worksheet, allowing you to visualize different trends and patterns in your data.
Click Manage Rules to edit or delete individual rules. This is especially useful if you have applied multiple rules to a worksheet.
Excel has a number of predefined styles, or presets, you can use to quickly apply conditional formatting to your data. They are grouped into three categories:
When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing data and allowing you to manipulate it in different ways.
Suppose we wanted to answer the question: “What is the amount sold by each salesperson?” for the sales data in the example below. Answering this question could be very time-consuming and difficult—each salesperson appears on multiple rows, and we would need to total all of their different orders individually. We could use the Subtotal command to help find the total for each salesperson, but we would still have a lot of data to work with.
Fortunately, a PivotTable can instantly calculate and summarize the data in a way that’s both easy to read and manipulate. When we’re done, the PivotTable will look something like this:
Once you’ve created a PivotTable, you can use it to answer different questions by rearranging, or pivoting, the data. For example, if we wanted to answer the question: “What is the total amount sold in each month?” we could modify our PivotTable to look like this:
Just like with normal spreadsheet data, you can sort the data in a PivotTable using the Sort & Filter command in the Home tab. You can also apply any type ofnumber formatting you want. For example, you may want to change the Number Format to Currency. However, be aware that some types of formatting may disappear when you modify the PivotTable.
If you change any of the data in your source worksheet, the PivotTable will not update automatically. To manually update it, select the PivotTable and then go to Analyze
Refresh.
One of the best things about PivotTables is that they can quickly pivot, or reorganize, data, allowing you to look at your worksheet data in different ways. Pivoting data can help you answer different questions and even experiment with the data to discover new trends and patterns.
In our example, we used the PivotTable to answer the question: “What is the total amount sold by each salesperson?” But now we’d like to answer a new question: “What is the total amount sold in each month?” We can do this by simply changing the field in the Rows area.
So far, our PivotTable has only shown one column of data at a time. In order to show multiple columns, you’ll need to add a field to the Columns area.
Sometimes you may want focus on just a certain section of your data. Filters can be used to narrow down the data in your PivotTable, allowing you to view only the information you need.
In our example, we’ll filter out certain salespeople to determine how they affect the total sales.
Slicers make filtering data in PivotTables even easier. Slicers are basically just filters, but they’re easier and faster to use, allowing you to instantly pivot your data . If you frequently filter your PivotTables, you may want to consider using slicers instead of filters.
You can also click the Filter icon in the upper-right corner to select all items from the slicer at once.
PivotCharts are like regular charts, except that they display data from a PivotTable. Just like regular charts, you’ll be able to select a chart type, layout, andstyle that will best represent the data.
In this example, our PivotTable is showing each person’s total sales per month. We’ll use a PivotChart so we can see the information more clearly.
Try using slicers or filters to change the data that is displayed. The PivotChart will automatically adjust to show the new data.
Excel includes many powerful tools to perform complex mathematical calculations, like what-if analysis. This feature can help you experiment and answer questions with your data, even when the data is incomplete. In this lesson, you will learn how to use a what-if analysis tool called Goal Seek.
Whenever you create a formula or function in Excel, you put various parts together to calculate a result. Goal Seek works in the opposite way: It lets you start with the desired result, and it calculates the input value that will give you that result. We’ll use a few examples to show how to use Goal Seek.
Let’s imagine you’re enrolled in a class. You currently have a grade of 65, and you need at least a 70 to pass the class. Luckily, you have one final assignment that might be able to raise your average. You can use Goal Seek to find out what grade you need on the final assignment to pass the class.
In the image below, you can see that the grades on the first four assignments are 58, 70, 72, and 60. Even though we don’t know what the fifth grade will be, we can write a formula, or function, that calculates the final grade. In this case, each assignment is weighted equally, so all we have to do is average all five grades by typing =AVERAGE(B2:B6). Once we use Goal Seek, cell B6 will show us the minimum grade we’ll need to make on that assignment.
Let’s imagine you’re planning an event and would like to invite as many people as you can without exceeding a budget of $500. We can use Goal Seek to figure out how many people to invite. In our example below, cell B4 contains the formula =B1+B2*B3 to calculate the total cost of a room reservation, plus the cost per person.
As you can see in the example above, some situations will require the answer to be a whole number. If Goal Seek gives you a decimal, you’ll need to round up or down, depending on the situation.
For more advanced projects, you may want to consider the other types of what-if analysis: scenarios and data tables. Rather than starting from the desired result and working backward, like Goal Seek, these options allow you to test multiple values and see how the results change.
For more information on scenarios, check out this article from Microsoft.
For more information on data tables, check out this article from Microsoft.
You can customize the Ribbon by creating your own tabs with whichever commands you want. Commands are always housed within a group, and you can create as many groups as you want in order to keep your tab organized. If you want, you can even add commands to any of the default tabs, as long as you create a custom group in the tab.
If you don’t see the command you want, click the Choose commands from: drop-down box and select All Commands.
If you’re working on a touch-screen device, you can enable Touch Mode to create more open space on the Ribbon, making commands easier to tap with your fingers.
To turn off Touch Mode, click the Touch/Mouse Mode command, then select Mouse from the drop-down menu.
Every Excel spreadsheet contains rows and columns. Most of the time, columns are identified by letters (A, B, C), and rows are identified by numbers (1, 2, 3). In Excel, this is known as the A1 reference style. However, some prefer to use a different method where columns are also identified by numbers. This is known as the R1C1 reference style.
While the R1C1 reference style is helpful for certain situations, you’ll probably want to use the A1 reference style most of the time. This tutorial will use the A1 reference style. If you’re currently using the R1C1 reference style, you’ll need to turn it off.
You can take the Excel quiz to see if you need to brush up on some of the training.
